package co.fitstart.scheduler.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.scheduler.Constant;
import co.fitstart.scheduler.entity.ExpiryRoundOrder;
import co.fitstart.scheduler.entity.FreezedCourseRound;
import co.fitstart.scheduler.persistence.GenericJdbcSupport;
import co.fitstart.scheduler.service.CourseRoundService;

@Service("courseRoundService")
public class CourseRoundServiceImpl implements CourseRoundService {

    private static final Logger logger = LoggerFactory.getLogger(CourseRoundServiceImpl.class);

    @Autowired
    private GenericJdbcSupport genericJdbcSupport;

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseScheduleService#expriyCourseRound(
     * java.util.Date)
     */
    public void expriyCourseRound(Date now) {
        try {

            expiryCourseRoundTransaction(now);

        } catch (Throwable t) {

            logger.error("expiryCourseRoundTransaction(): ", t);

        }
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.CourseRoundService#foreverExpriyCourseRound(java.util.Date)
     */
    @Override
    public void foreverExpriyCourseRound(Date beforeDate) {
        try {

            foreverExpiryCourseRoundTransaction(beforeDate);

        } catch (Throwable t) {

            logger.error("foreverExpriyCourseRound(): ", t);

        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseScheduleService#toExpiryCourseRound(
     * java.util.Date)
     */
    public int toExpiryCourseRound(Date now) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("today", now);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        return genericJdbcSupport.queryForInt("SELECT COUNT(*) FROM course_round "
                + "WHERE course_status = :ready AND effective_end IS NOT NULL AND effective_end < :today "
                + "AND course_type != :year AND is_disabled = 0", parameters);
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.CourseRoundService#toForverExpiryCourseRound(java.util.Date)
     */
    public int toForverExpiryCourseRound(Date beforeDate) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("ready", Constant.ROUND_STATUS_EXPIRE);
        parameters.put("today", beforeDate);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        return genericJdbcSupport.queryForInt("SELECT COUNT(*) FROM course_round "
                + "WHERE course_status = :ready AND effective_end IS NOT NULL AND effective_end < :today "
                + "AND course_type != :year AND is_disabled = 0", parameters);
    }

    /**
     * 
     * @param now
     */
    @Transactional
    public void expiryCourseRoundTransaction(Date now) {

        List<ExpiryRoundOrder> orders = listExpiryRoundOrder(now);
        completeExpiryRoundOrder(orders);

        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("expiry", Constant.ROUND_STATUS_EXPIRE);
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("today", now);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        genericJdbcSupport.executeSQL("UPDATE course_round SET course_status = :expiry "
                + "WHERE effective_end IS NOT NULL AND effective_end < :today AND course_status = :ready "
                + "AND course_type != :year AND is_disabled=0", parameters);
    }
    
    /**
     * 
     * @param now
     */
    @Transactional
    public void foreverExpiryCourseRoundTransaction(Date beforeDate) {

        List<ExpiryRoundOrder> orders = listExpiryRoundOrder(beforeDate);
        completeExpiryRoundOrder(orders);

        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("foreverexpiry", Constant.ROUND_STATUS_FORVER_EXPIRE);
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("today", beforeDate);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        genericJdbcSupport.executeSQL("UPDATE course_round SET course_status = :foreverexpiry "
                + "WHERE effective_end IS NOT NULL AND effective_end < :today AND course_status = :ready "
                + "AND course_type != :year AND is_disabled=0", parameters);
    }

    private List<ExpiryRoundOrder> listExpiryRoundOrder(Date now) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("today", now);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        return genericJdbcSupport.queryForList("SELECT round.order_id as orderId FROM course_round round "
                + "WHERE effective_end IS NOT NULL AND effective_end < :today AND course_status = :ready AND round.order_id IS NOT NULL "
                + "AND course_type != :year AND is_disabled=0", parameters, ExpiryRoundOrder.class);
    }

    private void completeExpiryRoundOrder(List<ExpiryRoundOrder> orders) {
        for (ExpiryRoundOrder expiryRoundOrder : orders) {
            Map<String, Object> parameters = new HashMap<String, Object>();
            parameters.put("complete", Constant.COMPLETE_ORDER_STATUS);
            parameters.put("orderId", expiryRoundOrder.getOrderId());
            genericJdbcSupport.executeSQL("UPDATE orders SET order_status = :complete "
                    + "WHERE id = :orderId AND is_disabled=0", parameters);
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseScheduleService#expriyCourseRound(
     * java.util.Date)
     */
    public void unfreezeCourseRound(Date now) {
        try {

            unfreezeCourseRoundTransaction(now);

        } catch (Throwable t) {

            logger.error("unfreezeCourseRoundTransaction(): ", t);

        }
    }

    /**
     * 
     * @param now
     */
    @Transactional
    public void unfreezeCourseRoundTransaction(Date now) {

        List<FreezedCourseRound> freezedCourseRounds = this.listFreezedCourseRound(now);
        for (FreezedCourseRound freezedCourseRound : freezedCourseRounds) {
            Map<String, Object> parameters = new HashMap<String, Object>();
            parameters.put("ready", Constant.ROUND_STATUS_READY);
            parameters.put("effectiveEnd", freezedCourseRound.calculateEffectiveEnd());
            parameters.put("courseRoundId", freezedCourseRound.getCourseRoundId());
            parameters.put("holdEndDate", now);

            genericJdbcSupport.executeSQL(
                    "UPDATE course_round SET course_status = :ready, effective_end = :effectiveEnd,hold_end_date=:holdEndDate "
                    + "WHERE id = :courseRoundId AND is_disabled=0", parameters);
        }

    }

    /**
     * 
     * @param now
     * @return
     */
    private List<FreezedCourseRound> listFreezedCourseRound(Date now) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("hold", Constant.ROUND_STATUS_HOLD);
        parameters.put("today", now);

        return genericJdbcSupport.queryForList(
                "SELECT round.id as courseRoundId, round.effective_end as effectiveEnd, round.hold_date as holdDate "
                + "FROM course_round round WHERE round.course_status = :hold AND round.hold_end_date IS NOT NULL "
                + "AND round.hold_end_date < :today AND round.is_disabled=0",
                parameters, FreezedCourseRound.class);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseRoundService#toUnfreezeCourseRound(
     * java.util.Date)
     */
    public int toUnfreezeCourseRound(Date now) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("hold", Constant.ROUND_STATUS_HOLD);
        parameters.put("today", now);
        return genericJdbcSupport.queryForInt("SELECT COUNT(*) FROM course_round "
                + "WHERE course_status = :hold AND hold_end_date IS NOT NULL AND hold_end_date < :today AND is_disabled=0",
                parameters);

    }

    /*
     * (non-Javadoc)
     * 
     * @see co.fitstart.scheduler.service.CourseRoundService#
     * toStopCampIndividualRound()
     */
    public int toStopCampIndividualRound() {
        List<Long> roundIdList = this.listToStopCampIndividualRound();
        if(roundIdList == null) {
            return 0;
        } else {
            return roundIdList.size();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseRoundService#stopCampIndividualRound(
     * )
     */
    @Transactional
    public void stopCampIndividualRound() {
        try {
            List<Long> individualRoundIdList = this.listToStopCampIndividualRound();
            
            for (Long individualRoundId : individualRoundIdList) {
            	
            	logger.info("# LZJ get  stopCampIndividualRound() individualRoundId [{}] ",individualRoundId);
                
                // 1. change courseStatus to finish
                Map<String, Object> courseRoundParameters = new HashMap<String, Object>();
                courseRoundParameters.put("finish", Constant.ROUND_STATUS_FINISH);
                courseRoundParameters.put("id", individualRoundId);
                genericJdbcSupport.executeSQL("UPDATE course_round SET course_status= :finish WHERE id= :id AND is_disabled=0",
                        courseRoundParameters);

                // 2. change status of order
                Map<String, Object> orderParameters = new HashMap<String, Object>();
                orderParameters.put("complete", Constant.ORDER_STATUS_COMPLETE);
                orderParameters.put("courseRoundId", individualRoundId);
                genericJdbcSupport.executeSQL("UPDATE orders SET order_status= :complete "
                        + "WHERE id  IN (SELECT order_id FROM course_round WHERE id= :courseRoundId AND order_id IS NOT NULL) "
                        + "AND is_disabled=0", orderParameters);
            }
        } catch (Throwable t) {

            logger.error("autoStopCampIndividualRound(): ", t);

        }
    }

    /**
     * 列出待自动结营1V1课程id
     * 
     * @return
     */
    private List<Long> listToStopCampIndividualRound() {
        List<Long> result = new ArrayList<>();
        
        //获取所有finished等于course_amount的1V1课程id
        Map<String, Object> roundParameters = new HashMap<String, Object>();
        roundParameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
       // roundParameters.put("ready", Constant.ROUND_STATUS_READY);
        roundParameters.put("hold", Constant.ROUND_STATUS_HOLD);
        roundParameters.put("expire", Constant.ROUND_STATUS_EXPIRE);
        roundParameters.put("courseAmount", Constant.FINISHED_COURSE_REPORT_COURSE_AMOUNT);
        //失效和暂停状态的课程
        List<Long> roundIdList = genericJdbcSupport.queryForSingleColumnList("SELECT cr.id FROM course_round cr "
                + "WHERE cr.course_type= :individual AND cr.course_status IN ( :hold, :expire) AND cr.is_disabled=0 "
                + "AND cr.finished=(SELECT cra.value FROM course_round_attr cra WHERE cra.course_round_id=cr.id AND cra.name=:courseAmount)", 
                roundParameters, Long.class);
//        List<Long> roundIdList = genericJdbcSupport.queryForSingleColumnList("SELECT cr.id FROM course_round cr "
//        		+ "WHERE cr.course_type= :individual AND cr.course_status IN ( :ready, :hold, :expire) AND cr.is_disabled=0 "
//        		+ "AND cr.finished=(SELECT cra.value FROM course_round_attr cra WHERE cra.course_round_id=cr.id AND cra.name=:courseAmount)", 
//        		roundParameters, Long.class);
        
        if(roundIdList != null && roundIdList.size() > 0) {
            //获取所有有未完成课时的1V1课程id
            Map<String, Object> scheduleParameters = new HashMap<String, Object>();
            scheduleParameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
            scheduleParameters.put("finish", Constant.SCHEDULE_STATUS_FINISH);
            List<Long> excludedRoundIdList = genericJdbcSupport.queryForSingleColumnList(
                    "SELECT distinct course_round_id FROM course_schedule "
                    + "WHERE course_type= :individual AND schedule_status!= :finish AND is_disabled=0", 
                    scheduleParameters, Long.class);
            
            if(excludedRoundIdList == null || excludedRoundIdList.isEmpty()) {
                result.addAll(roundIdList);
            } else {
                for(Long id : roundIdList) {
                    if(!excludedRoundIdList.contains(id)) {
                        result.add(id);
                    }
                }
            }
        }
        
        //逾期超过90天的课程
       // Date ninetyDaysAgo = DateUtils.addDays(new Date(), 90);
        
                
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("expire", Constant.ROUND_STATUS_EXPIRE);
       // parameters.put("effectiveEnd", ninetyDaysAgo);
        
      //逾期超过90天的课程
        result.addAll(genericJdbcSupport.queryForSingleColumnList("SELECT cr.id FROM course_round cr "
                + "WHERE cr.course_type= :individual AND cr.course_status =:expire AND cr.is_disabled=0 "
                + "AND DATE_ADD(cr.effective_end,INTERVAL 90 DAY) < now()", 
                parameters, Long.class));
        
//        result.addAll(genericJdbcSupport.queryForSingleColumnList("SELECT cr.id FROM course_round cr "
//        		+ "WHERE cr.course_type= :individual AND cr.course_status =:expire AND cr.is_disabled=0 "
//        		+ "AND cr.effective_end < :effectiveEnd", 
//        		parameters, Long.class));
        
        return result;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseRoundService#toStopCampYearRound(java
     * .util.Date)
     */
    public int toStopCampYearRound(Date now) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("today", now);
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("expire", Constant.ROUND_STATUS_EXPIRE);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        return genericJdbcSupport.queryForInt("SELECT COUNT(*) FROM course_round "
                + "WHERE effective_end IS NOT NULL AND effective_end< :today "
                + "AND course_status IN ( :ready, :expire) AND course_type= :year AND is_disabled=0",
                parameters);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * co.fitstart.scheduler.service.CourseRoundService#stopCampYearRound(java.
     * util.Date)
     */
    @Transactional
    public void stopCampYearRound(Date now) {
        try {
            //1. change status of order
            Map<String, Object> orderParameters = new HashMap<String, Object>();
            orderParameters.put("complete", Constant.ORDER_STATUS_COMPLETE);
            orderParameters.put("today", now);
            orderParameters.put("ready", Constant.ROUND_STATUS_READY);
            orderParameters.put("expire", Constant.ROUND_STATUS_EXPIRE);
            orderParameters.put("year", Constant.COURSE_TYPE_YEAR);
            
            genericJdbcSupport.executeSQL("UPDATE orders SET order_status= :complete WHERE id "
                    + "IN (SELECT order_id FROM course_round WHERE effective_end IS NOT NULL AND effective_end< :today "
                    + "AND course_status IN ( :ready, :expire) AND course_type= :year AND is_disabled=0 AND order_id IS NOT NULL) "
                    + "AND is_disabled=0", orderParameters);
            
            //2.change courseSchedule to disabled
            Map<String, Object> courseScheduleParameters = new HashMap<String, Object>();
            courseScheduleParameters.put("schedule", Constant.SCHEDULE_STATUS_SCHEDULE);
            courseScheduleParameters.put("scheduleReady", Constant.SCHEDULE_STATUS_READY);
            courseScheduleParameters.put("today", now);
            courseScheduleParameters.put("ready", Constant.ROUND_STATUS_READY);
            courseScheduleParameters.put("expire", Constant.ROUND_STATUS_EXPIRE);
            courseScheduleParameters.put("year", Constant.COURSE_TYPE_YEAR);
            
            genericJdbcSupport.executeSQL("UPDATE course_schedule SET is_disabled=1 WHERE is_disabled=0 "
                    + "AND schedule_status IN ( :schedule, :scheduleReady) "
                    + "AND course_round_id IN (SELECT id FROM course_round WHERE effective_end IS NOT NULL "
                    + "AND effective_end< :today AND course_status IN ( :ready, :expire) AND course_type= :year "
                    + "AND is_disabled=0 AND order_id IS NOT NULL)", courseScheduleParameters);
            
            //3.记录入营及结营时三围和inbody
            List<Map<String, Object>> yearRoundIds = genericJdbcSupport.queryForList("SELECT cr.id AS id,s.user_id AS user"
                    + " FROM course_round cr JOIN student s ON cr.student_id=s.id"
                    + " WHERE cr.effective_end IS NOT NULL AND cr.effective_end< :today"
                    + " AND cr.course_status IN ( :ready, :expire) AND cr.course_type= :year AND cr.is_disabled=0",
                    orderParameters);
            if(!yearRoundIds.isEmpty()) {
                Map<String, Object> dateParameters = new HashMap<>();
                dateParameters.put("finish", Constant.SCHEDULE_STATUS_FINISH);
                
                for(Map<String, Object> map : yearRoundIds) {
                    Long yearRoundId = (Long)map.get("id");
                    Long userId = (Long)map.get("user");
                    
                    dateParameters.put("courseRoundId", yearRoundId);
                    
                    List<Map<String, Object>> dates = genericJdbcSupport.queryForList("SELECT MIN(occur_date) AS begin,MAX(occur_date) AS end FROM course_schedule WHERE course_round_id=:courseRoundId AND schedule_status=:finish AND is_disabled=false", dateParameters);
                    
                    if(!dates.isEmpty() && dates.get(0).get("begin") != null && dates.get(0).get("end") != null) {
                        Date begin = (Date)dates.get(0).get("begin");
                        Date end = (Date)dates.get(0).get("end");
                        
                        Map<String, Object> recordParameters = new HashMap<>();
                        recordParameters.put("userId", userId);
                        recordParameters.put("begin", DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00");
                        recordParameters.put("end", DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59");
                        
                        List<Long> bwhRecords = genericJdbcSupport.queryForSingleColumnList("SELECT id FROM bwh_record"
                                + " WHERE is_disabled=false AND user_id=:userId AND record_time BETWEEN :begin AND :end"
                                + " ORDER BY record_time DESC", recordParameters, Long.class);
                        
                        List<Long> inbodyRecords = genericJdbcSupport.queryForSingleColumnList("SELECT id FROM inbody_record"
                                + " WHERE is_disabled=false AND user_id=:userId AND record_time BETWEEN :begin AND :end"
                                + " ORDER BY record_time DESC", recordParameters, Long.class);
                        
                        if(!bwhRecords.isEmpty() || !inbodyRecords.isEmpty()) {
                            Long previousBwhId = null;
                            Long laterBwhId = null;
                            Long previousInbodyId = null;
                            Long laterInbodyId = null;
                            
                            if(!bwhRecords.isEmpty()) {
                                previousBwhId = bwhRecords.get(bwhRecords.size() - 1);
                                laterBwhId = bwhRecords.get(0);
                            }
                            
                            if(!inbodyRecords.isEmpty()) {
                                previousInbodyId = inbodyRecords.get(inbodyRecords.size() - 1);
                                laterInbodyId = inbodyRecords.get(0);
                            }
                            
                            Map<String, Object> insertParameters = new HashMap<>();
                            insertParameters.put("courseRoundId", yearRoundId);
                            insertParameters.put("previousBwhId", previousBwhId);
                            insertParameters.put("laterBwhId", laterBwhId);
                            insertParameters.put("previousInbodyId", previousInbodyId);
                            insertParameters.put("laterInbodyId", laterInbodyId);
                            
                            genericJdbcSupport.executeSQL("INSERT INTO course_round_result(course_round_id, previous_bwh_id, later_bwh_id, previous_inbody_id, later_inbody_id)"
                                    + " values(:courseRoundId, :previousBwhId, :laterBwhId, :previousInbodyId, :laterInbodyId)", insertParameters);
                        }
                    }
                }
            }
            
            //4. change courseStatus to finish
            Map<String, Object> courseRoundParameters = new HashMap<String, Object>();
            courseRoundParameters.put("finish", Constant.ROUND_STATUS_FINISH);
            courseRoundParameters.put("today", now);
            courseRoundParameters.put("ready", Constant.ROUND_STATUS_READY);
            courseRoundParameters.put("expire", Constant.ROUND_STATUS_EXPIRE);
            courseRoundParameters.put("year", Constant.COURSE_TYPE_YEAR);
            genericJdbcSupport.executeSQL("UPDATE course_round SET course_status= :finish,finish_time= effective_end "
                    + "WHERE effective_end IS NOT NULL AND effective_end< :today "
                    + "AND course_status IN ( :ready, :expire) AND course_type= :year "
                    + "AND is_disabled=0", courseRoundParameters);
        } catch (Throwable t) {
            
            logger.error("autoStopCampYearRound(): ", t);
            
        }
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.CourseRoundService#recordNosigninCourseRound(java.util.Date)
     */
    public void recordNosigninCourseRound(Date now) {
        // 1. 获取四周前开始的所有课程
        Map<String, Object> allRoundpara = new HashMap<String, Object>();
        
        allRoundpara.put("ready", Constant.ROUND_STATUS_READY);
        allRoundpara.put("hold", Constant.ROUND_STATUS_HOLD);
        allRoundpara.put("expire", Constant.ROUND_STATUS_EXPIRE);
        allRoundpara.put("activate", Constant.ROUND_STATUS_ACTIVATE);
        allRoundpara.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        allRoundpara.put("year", Constant.COURSE_TYPE_YEAR);
        allRoundpara.put("begin", DateUtils.addWeeks(now, -4));
        
        List<Long> allRoundIds = genericJdbcSupport.queryForSingleColumnList(
                "select cr.id from course_round cr join course c on cr.course_id=c.id"
                + " where cr.is_disabled=false and cr.course_status in (:ready,:hold,:expire,:activate)"
                + " and cr.course_type in (:individual,:year) and c.is_experience=false"
                + " and cr.effective_start<= :begin", 
                allRoundpara, Long.class);
        
        // 2. 获取四周内有签到记录且开始时间为四周前的所有课程
        Map<String, Object> signinRoundpara = new HashMap<String, Object>();
        
        signinRoundpara.put("ready", Constant.ROUND_STATUS_READY);
        signinRoundpara.put("hold", Constant.ROUND_STATUS_HOLD);
        signinRoundpara.put("expire", Constant.ROUND_STATUS_EXPIRE);
        signinRoundpara.put("activate", Constant.ROUND_STATUS_ACTIVATE);
        signinRoundpara.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        signinRoundpara.put("year", Constant.COURSE_TYPE_YEAR);
        signinRoundpara.put("finish", Constant.SCHEDULE_STATUS_FINISH);
        signinRoundpara.put("begin", DateUtils.addWeeks(now, -4));
        signinRoundpara.put("end", now);
        
        List<Long> signinRoundIds = genericJdbcSupport.queryForSingleColumnList(
                "select distinct cr.id from course_round cr"
                + " join course c on cr.course_id=c.id"
                + " join course_schedule cs on cr.id=cs.course_round_id"
                + " join signin_record sr on cs.id=sr.course_schedule_id"
                + " where cr.is_disabled=false and cs.is_disabled=false and sr.is_disabled=false"
                + " and cr.course_status in (:ready,:hold,:expire,:activate)"
                + " and cr.course_type in (:individual,:year) and c.is_experience=false"
                + " and cs.schedule_status= :finish and sr.signin_time>= :begin"
                + " and sr.signin_time<= :end and cr.effective_start<= :begin", 
                signinRoundpara, Long.class);
        
        // 3. 记录四周内没有签到记录且开始时间为四周前的所有课程
        if(allRoundIds != null && !allRoundIds.isEmpty() && (signinRoundIds == null || allRoundIds.size() > signinRoundIds.size())) {
            for(Long roundId : allRoundIds) {
                if(signinRoundIds == null || !signinRoundIds.contains(roundId)) {
                    Map<String, Object> parameters = new HashMap<String, Object>();
                    
                    parameters.put("courseRoundId", roundId);
                    parameters.put("recordDate", DateUtils.parseDate(DateFormatUtils.format(now, "yyyy-MM-dd"), "yyyy-MM-dd"));
                    
                    genericJdbcSupport.executeSQL(
                            "insert into no_signin_course_round(course_round_id,record_date)"
                            + " values( :courseRoundId, :recordDate)", parameters);
                }
            }
        }
    }
}
